﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMLogistics.Model;
using DapperExtensions;
using Dapper;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using System.Reflection;
using WMLogistics.Model.QueryModel;
using Logistics.Model;

namespace WMLogistics_Dao
{
    public class WMBidLoadDao
    {
        private SqlGeneratorImpl sqlGenerator;
        public WMBidLoadDao()
        {
            var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect());
            sqlGenerator = new SqlGeneratorImpl(config);
            DapperExtensions.DapperExtensions.Configure(config);
        }
        public PageData<wuliu_wm_bid_load> GetBidLoadList(IPredicate predicate, int pageIndex, int pageSize)
        {
            IList<ISort> sort = new List<ISort>();
            sort.Add(Predicates.Sort<wuliu_wm_bid_load>(p => p.end_bid_time, true));
            var sql = " from wuliu_wm_bid_load  where 1=1 ";

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageData<wuliu_wm_bid_load>();
                DynamicParameters dynamicParameters = new DynamicParameters();
                var whereSql = GetWhereSql(predicate, dynamicParameters);
                if (!string.IsNullOrEmpty(whereSql))
                {
                    sql += " and " + whereSql;
                }
                var num = conn.ExecuteScalar<int>("select count(1) " + sql, dynamicParameters);

                sql += "order by wuliu_wm_bid_load.end_bid_time DESC limit @pageIndex,@pageSize";
                //dynamicParameters.AddDynamicParams(pickParam);
                dynamicParameters.Add("pageIndex", pageIndex * pageSize);
                dynamicParameters.Add("pageSize", pageSize);
                //var count = conn.Count<wuliu_wm_bid_load>(predicate);
                pageData.Items = conn.Query<wuliu_wm_bid_load>("select * " + sql, dynamicParameters).ToList();
                pageData.TotalNum = num;
                pageData.CurrentPage = pageIndex;
                pageData.TotalPageCount = num % pageSize == 0 ? num / pageSize : num / pageSize + 1;
                return pageData;
            }
        }

        public object GetWebLoadInfo(int load_id)
        {
            string sql = @"SELECT
	a.bid_id,
	a.bid_no,
	a.danger,
    a.state,
	a.contacts,
	a.contacts_phone,
	a.consignee_time,
	CONCAT(
		a.start_city_name,
		a.start_address
	) AS consignee_address,
	CONCAT(
		a.end_city_name,
		a.end_address
	) AS receipt_address,
	a.information,
	a.remark,
	a.staff,
	a.staff_phone,
    b.load_no,
    b.id as load_id,
	b.wuliu_name,
	b.price,
    b.load_state,
    b.load_time,
    b.receipt_time,
    b.consignee_time,
	b.driver_name,
	b.driver_phone
FROM
	wuliu_wm_bid_base a
LEFT JOIN wuliu_wm_bid_load b ON a.bid_id = b.bid_id where b.id=@load_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query(sql, new { load_id }).FirstOrDefault();
            }
        }
        /// <summary>
        /// 获取运单详情
        /// </summary>
        /// <param name="flag"></param>
        /// <param name="load_id"></param>
        /// <returns></returns>
        public object GetPhoneLoadInfo(int flag,int load_id)
        {
            string sql = "";
            if(flag==4)
            {
                sql = @"SELECT
	a.bid_id,
    a.bid_no,
	a.number,
	a.start_bid_time,
	a.end_bid_time,
	a.danger,
	a.wuliu_type,
	a.price_mode,
	a.fk,
	a.contacts,
	a.contacts_phone,
	a.consignee_time,
	CONCAT(
		a.start_city_name,
		a.start_address
	) AS consignee_address,
	a.receipt_time,
	CONCAT(
		a.end_city_name,
		a.end_address
	) AS receipt_address,
	a.information,
	a.remark,
	a.staff,
	a.staff_phone,
    b.load_no,
    b.id as load_id,
	b.wuliu_name,
	b.price,
	b.driver_name,
	b.driver_phone
FROM
	wuliu_wm_bid_base a
LEFT JOIN wuliu_wm_bid_load b ON a.bid_id = b.bid_id where b.id=@load_id";
            }
            if(flag==5)
            {
                sql = @"SELECT
	a.bid_id,
    a.bid_no,
	a.number,
	a.start_bid_time,
	a.end_bid_time,
	a.danger,
	a.wuliu_type,
	a.price_mode,
	a.fk,
	a.contacts,
	a.contacts_phone,
	a.consignee_time,
	CONCAT(
		a.start_city_name,
		a.start_address
	) AS consignee_address,
	a.receipt_time,
	CONCAT(
		a.end_city_name,
		a.end_address
	) AS receipt_address,
	a.information,
	a.remark,
	a.staff,
	a.staff_phone,
    b.load_no,
    b.id as load_id,
	b.wuliu_name,
	b.price,
	b.driver_name,
	b.driver_phone,
	b.back_photo_time,
	b.back_photo
FROM
	wuliu_wm_bid_base a
LEFT JOIN wuliu_wm_bid_load b ON a.bid_id = b.bid_id where b.id=@load_id ";
            }
            if(flag==6)
            {
                sql = @"SELECT
	a.bid_id,
    a.bid_no,
	a.number,
	a.start_bid_time,
	a.end_bid_time,
	a.danger,
	a.wuliu_type,
	a.price_mode,
	a.fk,
	a.contacts,
	a.contacts_phone,
	a.consignee_time,
	CONCAT(
		a.start_city_name,
		a.start_address
	) AS consignee_address,
	a.receipt_time,
	CONCAT(
		a.end_city_name,
		a.end_address
	) AS receipt_address,
	a.information,
	a.remark,
	a.staff,
	a.staff_phone,
    b.load_no,
    b.id as load_id,
	b.wuliu_name,
	b.price,
	b.driver_name,
	b.driver_phone,
	b.back_photo_time,
	b.back_photo,
    c.transport_status,
    c.goods_status,
    c.damage,
    case when c.damage_flag = '1' then b.price + c.damage else b.price - c.damage end as bill_price,
    d.report_id
 FROM wuliu_wm_bid_base a
 LEFT JOIN wuliu_wm_bid_load b ON a.bid_id = b.bid_id
 LEFT JOIN wuliu_bid_pickback c on b.bid_id = c.bid_id 
 LEFT JOIN wuliu_wm_report_base d on d.load_no=b.load_no
 where b.id=@load_id";
            }
            

            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query(sql, new { load_id }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 手机端获取列表
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public PageModel GetPhoneLoadList(IPredicate predicate, int pageIndex, int pageSize)
        {
            string sql = @"  from wuliu_wm_bid_load where 1=1 ";
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_contract_base>(p => p.id,true)
                                    };

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageModel();
                DynamicParameters dynamicParameters = new DynamicParameters();
                var whereSql = GetWhereSql(predicate, dynamicParameters);
                if (!string.IsNullOrEmpty(whereSql))
                {
                    sql += " and " + whereSql;
                }
                var num = conn.ExecuteScalar<int>(" select count(1) " + sql, dynamicParameters);

                sql += " order by wuliu_wm_bid_load.id DESC limit @pageIndex,@pageSize ";
                //dynamicParameters.AddDynamicParams(pickParam);
                dynamicParameters.Add("pageIndex", pageIndex * pageSize);
                dynamicParameters.Add("pageSize", pageSize);
                //var count = conn.Count<wuliu_wm_bid_load>(predicate);
                var data = conn.Query(@" select id as load_id,load_no,bid_id,bid_no,wuliu_name,consignee_time,receipt_time,CONCAT(start_city_name,start_address) as consignee_address,CONCAT(end_city_name,end_address) as receipt_address " + sql, dynamicParameters);
                pageData.datamod = data;
                pageData.totalnum = num;
                pageData.currentpage = pageIndex;
                pageData.totalpagecount = num % pageSize == 0 ? num / pageSize : num / pageSize + 1;
                return pageData;
            }

        }
       

        private string GetWhereSql(IPredicate predicate, DynamicParameters dynamicParameters)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            var wherSql = predicate.GetSql(sqlGenerator, parameters);


            foreach (var parameter in parameters)
            {
                dynamicParameters.Add(parameter.Key, parameter.Value);
            }
            return wherSql;
        }


        public PageData<wuliu_wm_bid_load> GetBidLoadList(BidLoadQuery query)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageDate = new PageData<wuliu_wm_bid_load>();
                //var count = conn.Count<wuliu_wm_bid_load>(predicate);
                return pageDate;
            }
        }

        public bool UpdateRemark(int id, string load_remark)
        {
            var sql = "update wuliu_wm_bid_load set load_remark=@load_remark where id=@id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { load_remark, id }) == 1;
            }
        }

        /// <summary>
        /// 关闭运单
        /// </summary>
        /// <param name="closeBid"></param>
        /// <returns></returns>
        public bool ClsoeBid(int bid_id, string load_no, int state, string del_flag)
        {
            string sql = "Update wuliu_wm_bid_load set load_state=100,bid_flag=@del_flag,bid_state=@state where bid_id=@bid_id and load_no=@load_no";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { bid_id, load_no, del_flag, state }) == 1;
            }
        }
        /// <summary>
        /// 运单已取货
        /// </summary>
        /// <param name="closeBid"></param>
        /// <returns></returns>
        public bool BidPick(string load_no)
        {
            string sql = "Update wuliu_wm_bid_load set load_state=20 where load_no=@load_no";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { load_no }) == 1;
            }
        }

        /// <summary>
        /// 未取货废弃原来的运单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool NotPickUp(string load_no)
        {
            string sql = "Update wuliu_wm_bid_load set bid_flag=10 where load_no=@load_no  ";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { load_no }) > 0;
            }
        }


        /// <summary>
        /// 修改运单状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool EditLoadState(string load_no, int loadstate, DateTime pickup_time)
        {
            string sql = "Update wuliu_wm_bid_load set load_state=@loadstate,pickup_time=@pickup_time where load_no=@load_no  ";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { loadstate, load_no, pickup_time }) > 0;
            }
        }

        /// <summary>
        /// 添加运单日志
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public bool AddBidLoad(wuliu_bid_load_log log)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var picId = (int)conn.Insert<wuliu_bid_load_log>(log);
                return picId > 0;
            }
        }


        public bool Pickback(wuliu_bid_pickback pickback)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var picId = (int)conn.Insert<wuliu_bid_pickback>(pickback);
                return picId > 0;
            }
        }
        /// <summary>
        /// 通过物流id和公司编号获取装货数据
        /// </summary>
        /// <param name="wuliu_id"></param>
        /// <param name="company_no"></param>
        /// <returns></returns>
        public wuliu_wm_bid_load GetBidLoadList(int wuliu_id, string company_no)
        {
            List<wuliu_wm_bid_load> list = new List<wuliu_wm_bid_load>();
            var sql = "select * from wuliu_wm_bid_load  where wuliu_id=@wuliu_id and company_no=@company_no";

            DynamicParameters dynamicParameters = new DynamicParameters();
            dynamicParameters.Add("wuliu_id", wuliu_id);
            dynamicParameters.Add("company_no", company_no);
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query<wuliu_wm_bid_load>(sql, dynamicParameters).ToList().FirstOrDefault();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="load_id"></param>
        /// <returns></returns>
        public wuliu_wm_bid_load GetBidLoadDeltial(int load_id)
        {

            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Get<wuliu_wm_bid_load>(load_id);
            }
        }



        public wuliu_wm_bid_load GetBidLoad(string load_no)
        {
            //using (var conn = ConnectionHelper.OpenConnection())
            //{
            //    return conn.Get<wuliu_wm_bid_load>(load_no);
            //}
            DynamicParameters dynamicParameters = new DynamicParameters();
            var sql = "select * from wuliu_wm_bid_load  where load_no=@load_no";
            dynamicParameters.Add("load_no", load_no);

            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query<wuliu_wm_bid_load>(sql, dynamicParameters).ToList().FirstOrDefault();
            }
        }

        public PageData<wuliu_wm_bid_load> GetTransportList()
        { 
             var pageData = new PageData<wuliu_wm_bid_load>();
             return pageData;
        }

        /// <summary>
        /// 获取装货列表
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public PageData<wuliu_wm_bid_load> GetTranspotList(IPredicate predicate, int page, int size)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_load>(p => p.end_bid_time,true)
                                    };
            var sql = @"select * from wuliu_wm_bid_load ";

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageData<wuliu_wm_bid_load>();
                DynamicParameters dynamicParameters = new DynamicParameters();
                var wherSql = GetWhereSql(predicate, dynamicParameters);
                if (!string.IsNullOrEmpty(wherSql))
                    sql += " where " + wherSql;
                var count = conn.Query<wuliu_wm_bid_load>(sql, dynamicParameters).Count();
                sql += " order by wuliu_wm_bid_load.end_bid_time DESC limit @page,@size";
                dynamicParameters.Add("page", page*size);
                dynamicParameters.Add("size", size);
                pageData.Items = conn.Query<wuliu_wm_bid_load>(sql, dynamicParameters).ToList();
                pageData.TotalNum = count;
                pageData.CurrentPage = page;
                pageData.TotalPageCount = count % size == 0 ? count / size : count / size + 1;
                return pageData;
            }
        }

        /// <summary>
        /// 获取装货列表
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public int GetTranspotCount(IPredicate predicate)
        {
           
            var sql = @"select * from wuliu_wm_bid_load ";

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageData<wuliu_wm_bid_load>();
                DynamicParameters dynamicParameters = new DynamicParameters();
                var wherSql = GetWhereSql(predicate, dynamicParameters);
                if (!string.IsNullOrEmpty(wherSql))
                    sql += " where " + wherSql;
                var count = conn.Query<wuliu_wm_bid_load>(sql, dynamicParameters).Count();
                return count;
            }
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="newLoad"></param>
        /// <returns></returns>
        public int Add(wuliu_wm_bid_load newLoad)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Insert<wuliu_wm_bid_load>(newLoad);
            }
        }

       
    }
}
